﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using XiaoGang.Soft.XGWMS.MODEL;
using XiaoGang.Soft.XGWMS.DBUtility;
using XiaoGang.Soft.XGWMS.COM;

namespace XiaoGang.Soft.XGWMS.DAL_SQL
{
    public class DAL_M003_USER
    {
        /// <summary>
        /// GetUserInfo
        /// </summary>
        /// <param name="USER_LOGIN_ID"></param>
        /// <returns></returns>
        public ReturnValue GetUserInfo(string USER_LOGIN_ID)
        {
            SqlParameter[] parameters = {new SqlParameter("@USER_LOGIN_ID", SqlDbType.Char,50)};
            parameters[0].Value = USER_LOGIN_ID;
            return SqlHelper.Query(DAL_M003_USER_SQL.GetUserInfo, parameters);
        }

        /// <summary>
        /// GetUserList
        /// </summary>
        /// <param name="modUser"></param>
        /// <returns></returns>
        public ReturnValue GetUserList(MOD_M003_USER modUser)
        {
            StringBuilder strWhere = new StringBuilder();
            if (modUser.USER_ID.Trim() != "")//编号
            {
                strWhere.Append(" AND M003.USER_ID Like '%" + modUser.USER_ID.Trim() + "%'");
            }
            if (modUser.USER_LOGIN_ID.Trim() != "")//登录ID
            {
                strWhere.Append(" AND M003.USER_LOGIN_ID LIKE '%" + modUser.USER_LOGIN_ID.Trim() + "%'");
            }
            if (modUser.USER_NAME.Trim() != "")//姓名
            {
                strWhere.Append(" AND M003.USER_NAME LIKE '%" + modUser.USER_NAME.Trim() + "%'");
            }
            if (modUser.USER_SEX.Trim() != "")
            {
                strWhere.Append(" AND  M003.USER_SEX  = '" + modUser.USER_SEX.Trim() + "'");
            }
            if (modUser.USER_STATE.Trim() != "")
            {
                strWhere.Append(" AND  M003.USER_STATE  = '" + modUser.USER_STATE.Trim() + "'");
            }
            if (modUser.USER_PHONE1.Trim() != "")
            {
                strWhere.Append(" AND (M003.USER_PHONE1  Like '%" + modUser.USER_PHONE1.Trim() + "%' Or M003.USER_PHONE2  Like '%" + modUser.USER_PHONE1.Trim() + "%')");
            }
            if (modUser.USER_ADDRESS1.Trim() != "")
            {
                strWhere.Append(" AND (M003.USER_ADDRESS1 LIKE '%" + modUser.USER_ADDRESS1.Trim() + "%' Or M003.USER_ADDRESS2 LIKE '%" + modUser.USER_ADDRESS1.Trim() + "%')");
            }            
            if (modUser.USER_EMAIL.Trim() != "")
            {
                strWhere.Append(" AND M003.USER_EMAIL  Like '%" + modUser.USER_EMAIL.Trim() + "%'");
            }
            if (modUser.USER_POST.Trim() != "")
            {
                strWhere.Append(" AND M003.USER_POST  Like '%" + modUser.USER_POST.Trim() + "%'");
            }
            if (modUser.USER_GROUP.Trim() != "")
            {
                strWhere.Append(" AND M003.USER_GROUP  = '" + modUser.USER_GROUP.Trim() + "'");
            }
            if (modUser.MEMO.Trim() != "")
            {
                strWhere.Append(" AND M003.MEMO LIKE '%" + modUser.MEMO.Trim() + "%'");
            }
            return DBUtility.SqlHelper.Query(DAL_M003_USER_SQL.GetUserList + strWhere);
        }

        /// <summary>
        /// AddUser
        /// </summary>
        /// <param name="modUser"></param>
        /// <returns></returns>
        public ReturnValue AddUser(MOD_M003_USER modUser)
        {
            SqlParameter[] parameters = {
                new SqlParameter("@UPDATER_ID", SqlDbType.VarChar,12),
                new SqlParameter("@TERMINAL_CD", SqlDbType.VarChar,256),
                new SqlParameter("@USER_ID", SqlDbType.Char,12),
                new SqlParameter("@USER_LOGIN_ID", SqlDbType.Char,50),
                new SqlParameter("@USER_WD", SqlDbType.Char,50),
                new SqlParameter("@USER_NAME", SqlDbType.VarChar,256),
                new SqlParameter("@USER_SEX", SqlDbType.Char,1),
                new SqlParameter("@USER_BIRTHDAY", SqlDbType.Char,8),
                new SqlParameter("@USER_PHONE1", SqlDbType.Char,20),
                new SqlParameter("@USER_PHONE2", SqlDbType.Char,20),
                new SqlParameter("@USER_ADDRESS1", SqlDbType.VarChar,256),
                new SqlParameter("@USER_ADDRESS2", SqlDbType.VarChar,256),
                new SqlParameter("@USER_EMAIL", SqlDbType.VarChar,50),
                new SqlParameter("@USER_POST", SqlDbType.Char,10),
                new SqlParameter("@USER_PHOTO", SqlDbType.Image),
                new SqlParameter("@USER_GROUP", SqlDbType.Char,12),
                new SqlParameter("@USER_STATE", SqlDbType.Char,1),
                new SqlParameter("@MEMO", SqlDbType.VarChar,256)};
            parameters[0].Value = modUser.UPDATER_ID;
            parameters[1].Value = modUser.TERMINAL_CD;
            parameters[2].Value = modUser.USER_ID;
            parameters[3].Value = modUser.USER_LOGIN_ID;
            parameters[4].Value = modUser.USER_WD;
            parameters[5].Value = modUser.USER_NAME;
            parameters[6].Value = modUser.USER_SEX;
            parameters[7].Value = modUser.USER_BIRTHDAY;
            parameters[8].Value = modUser.USER_PHONE1;
            parameters[9].Value = modUser.USER_PHONE2;
            parameters[10].Value = modUser.USER_ADDRESS1;
            parameters[11].Value = modUser.USER_ADDRESS2;
            parameters[12].Value = modUser.USER_EMAIL;
            parameters[13].Value = modUser.USER_POST;
            if (modUser.USER_PHOTO == null)
            {
                parameters[14].Value = DBNull.Value;
            }
            else
            {
                parameters[14].Value = modUser.USER_PHOTO;
            }
            parameters[15].Value = modUser.USER_GROUP;
            parameters[16].Value = modUser.USER_STATE;
            parameters[17].Value = modUser.MEMO;
            return DBUtility.SqlHelper.ExecuteSql(DAL_M003_USER_SQL.AddUser, parameters);
        }

        /// <summary>
        /// GetUserPhoto
        /// </summary>
        /// <param name="USER_ID"></param>
        /// <returns></returns>
        public ReturnValue GetUserPhoto(string USER_ID)
        {
            SqlParameter[] parameters = { new SqlParameter("@USER_ID", SqlDbType.Char, 12) };
            parameters[0].Value = USER_ID;
            return SqlHelper.Query(DAL_M003_USER_SQL.GetUserPhoto, parameters);
        }

        /// <summary>
        /// DelUser
        /// </summary>
        /// <param name="USER_ID"></param>
        /// <returns></returns>
        public ReturnValue DelUser(string USER_ID)
        {
            SqlParameter[] parameters = { new SqlParameter("@USER_ID", SqlDbType.Char, 12) };
            parameters[0].Value = USER_ID;
            return SqlHelper.ExecuteSql(DAL_M003_USER_SQL.DelUser, parameters);
        }

        /// <summary>
        /// UpdateUser
        /// </summary>
        /// <param name="modUser"></param>
        /// <returns></returns>
        public ReturnValue UpdateUser(MOD_M003_USER modUser)
        {
            SqlParameter[] parameters = {
                new SqlParameter("@UPDATER_ID", SqlDbType.VarChar,12),
                new SqlParameter("@TERMINAL_CD", SqlDbType.VarChar,256),
                new SqlParameter("@USER_ID", SqlDbType.Char,12),
                new SqlParameter("@USER_LOGIN_ID", SqlDbType.Char,50),
                new SqlParameter("@USER_WD", SqlDbType.Char,50),
                new SqlParameter("@USER_NAME", SqlDbType.VarChar,256),
                new SqlParameter("@USER_SEX", SqlDbType.Char,1),
                new SqlParameter("@USER_BIRTHDAY", SqlDbType.Char,8),
                new SqlParameter("@USER_PHONE1", SqlDbType.Char,20),
                new SqlParameter("@USER_PHONE2", SqlDbType.Char,20),
                new SqlParameter("@USER_ADDRESS1", SqlDbType.VarChar,256),
                new SqlParameter("@USER_ADDRESS2", SqlDbType.VarChar,256),
                new SqlParameter("@USER_EMAIL", SqlDbType.VarChar,50),
                new SqlParameter("@USER_POST", SqlDbType.Char,10),
                new SqlParameter("@USER_PHOTO", SqlDbType.Image),
                new SqlParameter("@USER_GROUP", SqlDbType.Char,12),
                new SqlParameter("@USER_STATE", SqlDbType.Char,1),
                new SqlParameter("@MEMO", SqlDbType.VarChar,256)};
            parameters[0].Value = modUser.UPDATER_ID;
            parameters[1].Value = modUser.TERMINAL_CD;
            parameters[2].Value = modUser.USER_ID;
            parameters[3].Value = modUser.USER_LOGIN_ID;
            parameters[4].Value = modUser.USER_WD;
            parameters[5].Value = modUser.USER_NAME;
            parameters[6].Value = modUser.USER_SEX;
            parameters[7].Value = modUser.USER_BIRTHDAY;
            parameters[8].Value = modUser.USER_PHONE1;
            parameters[9].Value = modUser.USER_PHONE2;
            parameters[10].Value = modUser.USER_ADDRESS1;
            parameters[11].Value = modUser.USER_ADDRESS2;
            parameters[12].Value = modUser.USER_EMAIL;
            parameters[13].Value = modUser.USER_POST;
            if (modUser.USER_PHOTO == null)
            {
                parameters[14].Value = DBNull.Value;
            }
            else
            {
                parameters[14].Value = modUser.USER_PHOTO;
            }
            parameters[15].Value = modUser.USER_GROUP;
            parameters[16].Value = modUser.USER_STATE;
            parameters[17].Value = modUser.MEMO;
            return DBUtility.SqlHelper.ExecuteSql(DAL_M003_USER_SQL.UpdateUser, parameters);
        }

        /// <summary>
        /// CheckUserExist
        /// </summary>
        /// <param name="USER_ID"></param>
        /// <returns></returns>
        public ReturnValue CheckUserExist(string UserID)
        {
            string strWhere = string.Empty;
            if (!string.IsNullOrEmpty(UserID))
            {
                strWhere += String.Format(" and USER_ID = '{0}'", UserID);
            }
            return SqlHelper.Query(DAL_M003_USER_SQL.CheckUserExist+strWhere);
        }

        /// <summary>
        /// 检查用户是否正在使用
        /// </summary>
        /// <param name="UserID"></param>
        /// <returns></returns>
        public ReturnValue CheckUserUsed(string UserID)
        {
            SqlParameter[] parameters = { new SqlParameter("@UserID", SqlDbType.Char, 12) };
            parameters[0].Value = UserID;
            return SqlHelper.Query(DAL_M003_USER_SQL.CheckUserUsed,parameters);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="UserID"></param>
        /// <param name="UserName"></param>
        /// <returns></returns>
        public ReturnValue CheckUserNameWithoutID(string UserID, string UserName)
        {
            string strWhere = string.Empty;
            if (!string.IsNullOrEmpty(UserID))
            {
                strWhere += String.Format(" and USER_ID <> '{0}'", UserID);
            }

            if (!string.IsNullOrEmpty(UserName))
            {
                strWhere += String.Format(" and USER_NAME = '{0}'", UserName);
            }
            return SqlHelper.Query(DAL_M003_USER_SQL.CheckUserExist+strWhere);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="USER_ID"></param>
        /// <param name="USER_LOGIN_ID"></param>
        /// <returns></returns>
        public ReturnValue CheckUserLoginIDWithoutID(string UserID, string UserLoginID)
        {
            string strWhere = string.Empty;
            if (!string.IsNullOrEmpty(UserID))
            {
                strWhere += String.Format(" and USER_ID <> '{0}'", UserID);
            }

            if (!string.IsNullOrEmpty(UserLoginID))
            {
                strWhere += String.Format(" and USER_LOGIN_ID = '{0}'", UserLoginID);
            }
            return SqlHelper.Query(DAL_M003_USER_SQL.CheckUserExist+strWhere);
        }
    }
}
